package edu.ynu.se.xiecheng.achitectureclass.common.utils.test;

import org.springframework.http.*;
import org.springframework.web.client.RestTemplate;
import com.fasterxml.jackson.databind.ObjectMapper;

import java.sql.*;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.*;

public class DeviceDataFetcher0 {

    // 数据库连接信息
    private static final String DB_URL = "jdbc:mysql://132.232.152.65:3306/20221120114?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
    private static final String DB_USER = "20221120114";
    private static final String DB_PASSWORD = "20221120114";

    // 获取设备采集的数据
    public static List<Map<String, Object>> fetchDeviceData(String token, String boxNo, List<String> ids) {
        // API URL
        String url = "https://fbox360.com/api/v2/dmon/value/get?boxNo=" + boxNo;

        // 请求头配置
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_JSON);
        headers.setBearerAuth(token); // 设置 Bearer Token

        // 请求体配置
        Map<String, Object> requestBody = new HashMap<>();
        requestBody.put("ids", ids);

        try {
            // 将请求体转换为 JSON 格式
            ObjectMapper objectMapper = new ObjectMapper();
            String requestBodyJson = objectMapper.writeValueAsString(requestBody);
            System.out.println("Request Body: " + requestBodyJson);

            // 创建请求实体
            HttpEntity<String> requestEntity = new HttpEntity<>(requestBodyJson, headers);

            // 发送 POST 请求
            RestTemplate restTemplate = new RestTemplate();
            ResponseEntity<List> response = restTemplate.exchange(url, HttpMethod.POST, requestEntity, List.class);

            // 判断响应状态
            if (response.getStatusCode() == HttpStatus.OK && response.getBody() != null) {
                return response.getBody(); // 返回数据
            } else {
                throw new RuntimeException("Failed to fetch device data. Status code: " + response.getStatusCode());
            }
        } catch (Exception e) {
            System.out.println("Error occurred: " + e.getMessage());
            throw new RuntimeException("Failed to fetch device data.", e);
        }
    }

    // 将数据存入数据库
    public static void saveDataToDatabase(List<Map<String, Object>> deviceData) {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            for (Map<String, Object> data : deviceData) {
                // 获取数据中的字段
                String sensorId = (String) data.getOrDefault("id", ""); // 传感器 ID
                String boxId = (String) data.getOrDefault("boxId", ""); // 设备箱 ID
                String dataType = String.valueOf(data.getOrDefault("dataType", "")); // 数据类型
                String name = (String) data.getOrDefault("name", ""); // 数据名称
                Double value = data.get("value") instanceof Number ? ((Number) data.get("value")).doubleValue() : null; // 数据值
                Integer status = data.get("status") instanceof Number ? ((Number) data.get("status")).intValue() : null; // 状态
                Integer connState = data.get("connState") instanceof Number ? ((Number) data.get("connState")).intValue() : null; // 连接状态
                String connStateTimestampStr = (String) data.getOrDefault("connStateTimestamp", null); // 连接状态时间
                String timestampStr = (String) data.getOrDefault("timestamp", null); // 数据采集时间

                // 转换时间戳
                Timestamp connStateTimestamp = null;
                Timestamp sqlTimestamp = null;
                if (connStateTimestampStr != null) {
                    connStateTimestamp = convertToSqlTimestamp(connStateTimestampStr);
                }
                if (timestampStr != null) {
                    sqlTimestamp = convertToSqlTimestamp(timestampStr);
                }

                // 获取表名
                String tableName = getTableNameBySensorId(sensorId);
                if (tableName != null) {
                    // 使用 INSERT ... ON DUPLICATE KEY UPDATE
                    String sql = "INSERT INTO " + tableName +
                            " (id, box_id, data_type, name, value, status, conn_state, conn_state_timestamp, timestamp, create_time) " +
                            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW()) " +
                            "ON DUPLICATE KEY UPDATE " +
                            "box_id = VALUES(box_id), data_type = VALUES(data_type), name = VALUES(name), value = VALUES(value), " +
                            "status = VALUES(status), conn_state = VALUES(conn_state), conn_state_timestamp = VALUES(conn_state_timestamp), " +
                            "timestamp = VALUES(timestamp), create_time = NOW()";

                    try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                        // 设置参数
                        stmt.setString(1, sensorId); // 传感器 ID
                        stmt.setString(2, boxId); // 设备箱 ID
                        stmt.setString(3, dataType); // 数据类型
                        stmt.setString(4, name); // 数据名称
                        stmt.setObject(5, value); // 数据值
                        stmt.setObject(6, status); // 状态
                        stmt.setObject(7, connState); // 连接状态
                        stmt.setTimestamp(8, connStateTimestamp); // 连接状态时间
                        stmt.setTimestamp(9, sqlTimestamp); // 数据采集时间
                        stmt.executeUpdate(); // 执行插入或更新
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("Database error: " + e.getMessage());
        }
    }



    // 根据传感器 ID 获取对应的表名
    private static String getTableNameBySensorId(String sensorId) {
        switch (sensorId) {
            case "327061330933584660":
                return "humidity_data"; // 土壤含水率表
            case "327061330933584661":
                return "temperature_data"; // 土壤温度表
            case "327061330932536075":
                return "conductivity_data"; // 土壤电导率表
            case "327061330932536076":
                return "ph_data"; // 土壤 PH 值表
            case "327061330932536077":
                return "nitrogen_data"; // 土壤氮含量表
            default:
                return null; // 未知传感器 ID
        }
    }

    // 将 ISO 8601 格式的时间字符串转换为 java.sql.Timestamp
    private static Timestamp convertToSqlTimestamp(String isoTimestamp) {
        try {
            // 使用 Instant 解析 ISO 8601 时间
            Instant instant = Instant.parse(isoTimestamp);
            LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
            return Timestamp.valueOf(localDateTime);
        } catch (Exception e) {
            System.out.println("Error parsing timestamp: " + isoTimestamp + " - " + e.getMessage());
            return null; // 如果解析失败，返回 null
        }
    }

    public static void main(String[] args) {
        // 获取的 Token
        String token = FBoxTokenFetcher.fetchToken(); // 调用之前获取 Token 的方法

        // BoxNo
        String boxNo = "300023040071";

        // 传感器 IDs
        List<String> ids = Arrays.asList(
                "327061330933584660", // 土壤含水率
                "327061330933584661", // 土壤温度
                "327061330932536075", // 土壤电导率
                "327061330932536076", // 土壤 PH 值
                "327061330932536077"  // 土壤氮含量
        );

        // 获取设备数据
        List<Map<String, Object>> deviceData = fetchDeviceData(token, boxNo, ids);

        // 打印获取到的数据
        for (Map<String, Object> data : deviceData) {
            System.out.println(data);
        }

        // 将数据存入数据库
        saveDataToDatabase(deviceData);
    }
}
